﻿CREATE procedure [dbo].[spReportWeeklySystemVariance]
	@yearPosition int,
	@WeekNumber int,
	@Month int
AS

DECLARE @year int
SET @year = YEAR(GETDATE()) + @yearPosition
set ansi_nulls OFF

DECLARE @MondayDate date
SELECT @MondayDate = DATEADD(DAY, -DATEPART(weekday, GETDATE()) + 2, CONVERT(date, GETDATE()))

DECLARE @weekNumberMonday smallint
SET @weekNumberMonday = datepart(day, datediff(day, 0, @MondayDate)/7 * 7)/7 + 1

SET @MondayDate = DATEADD(DAY, (@WeekNumber - @weekNumberMonday - 1) * 7, @MondayDate)

DECLARE @query nvarchar(max)

SET @query = 'SELECT 
	PeriodYear as [Period Year]
	, ProgramId as [Program Id]
	, ProgramName as [Program Name]
	, ProgramManager as [Program Manager]
	, ProjectId as [Project Id]
	, ProjectName as [Project Name]
	, AccountLevel1 as [Account Level 1]
	, BowFlag as [Bow Flag]
	, SystemGocCode as [System GOC Code]
	, ManagedGeography as [System Geography Code]
	, ISNULL(Value0, 0) as [Value]
	, ISNULL(Value1, 0) as [Value ]
	, ISNULL(Value1 - Value0, 0) as [Variance ]
	, ISNULL(Value2, 0) as [ Value]
	, ISNULL(Value2 - Value1, 0) as [ Variance]
	, ISNULL(Value3, 0) as [ Value ]
	, ISNULL(Value3 - Value2, 0) as [ Variance ]
	, ISNULL(Value4, 0) as [ Value  ]
	, ISNULL(Value4 - Value3, 0) as [ Variance  ]
	, ISNULL(Value5, 0) as [  Value  ]
	, ISNULL(Value5 - Value4, 0) as [  Variance  ]
from BookOfWorkSystemVariance
Where PeriodYear = ' + CONVERT(varchar(4), @year) + '
 AND Month = ' + CONVERT(varchar(2), @Month) + ' 
 AND ReferenceDate = ''' + CONVERT(varchar(100),@MondayDate) + ''''
 --AND WeekNumber = ' + CONVERT(char(1), @WeekNumber) + ' 

EXEC (@query)